package dao.impl;

import com.mysql.cj.util.StringUtils;
import dao.BaseDao;
import dao.User_ManagementDao;
import entity.User_Management;

import javax.naming.NamingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class User_MangementDaoimpl extends BaseDao implements User_ManagementDao {

    @Override
    public List<User_Management> management(String username, int pageSize, int pageNum)
            throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User_Management> list = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder(
                    "SELECT user_id, username, password, role, phone, created_at, sex " +
                            "FROM users WHERE (role='dormitory' OR role='student')");

            if (!StringUtils.isNullOrEmpty(username)) {
                sql.append(" AND username LIKE ?");
            }

            // 确保分页参数有效
            if (pageNum < 1) pageNum = 1;
            if (pageSize < 1) pageSize = 10;
            int offset = (pageNum - 1) * pageSize;

            sql.append(" LIMIT ?, ?");

            ps = conn.prepareStatement(sql.toString());
            int paramIndex = 1;
            if (!StringUtils.isNullOrEmpty(username)) {
                ps.setString(paramIndex++, "%" + username + "%");
            }
            ps.setInt(paramIndex++, offset);
            ps.setInt(paramIndex, pageSize);

            rs = ps.executeQuery();
            while (rs.next()) {
                User_Management um = new User_Management();
                um.setUser_id(rs.getInt("user_id"));
                um.setUsername(rs.getString("username"));
                um.setPassword(rs.getString("password"));
                um.setRole(rs.getString("role"));
                um.setPhone(rs.getString("phone"));
                um.setCreated_at(rs.getString("created_at"));
                um.setSex(rs.getString("sex"));
                list.add(um);
            }
            return list;
        } finally {
            closeResources(rs, ps, conn);
        }
    }

    @Override
    public int INSERT(String username, String password, String role, String phone, String sex)
            throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;

        try {

            String sql = "INSERT INTO users (username, password, role, phone, sex) VALUES (?, ?, ?, ?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, password);
            ps.setString(3, role);
            ps.setString(4, phone);
            ps.setString(5, sex);
            return ps.executeUpdate();
        } finally {
            closeResources(null, ps, conn);
        }
    }

    @Override
    public int UPDATE(int user_id, String username, String password, String role,
                      String phone, String created_at, String sex)
            throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;

        try {

            String sql = "UPDATE users SET username = ?, role = ?, phone = ?, " +
                    "created_at = ?, sex = ? WHERE user_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, role);
            ps.setString(3, phone);
            ps.setString(4, created_at);
            ps.setString(5, sex);
            ps.setInt(6, user_id);
            return ps.executeUpdate();
        } finally {
            closeResources(null, ps, conn);
        }
    }

    @Override
    public int delete(int user_id) throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement pt = null;

        try {

            String sql = "DELETE FROM users WHERE user_id = ?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, user_id);
            return pt.executeUpdate();
        } finally {
            closeResources(null, pt, conn);
        }
    }

    @Override
    public int getTotalCount(String username) throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            StringBuilder sql = new StringBuilder(
                    "SELECT COUNT(*) FROM users WHERE (role='dormitory' OR role='student')");

            if (!StringUtils.isNullOrEmpty(username)) {
                sql.append(" AND username LIKE ?");
            }

            ps = conn.prepareStatement(sql.toString());
            if (!StringUtils.isNullOrEmpty(username)) {
                ps.setString(1, "%" + username + "%");
            }

            rs = ps.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            closeResources(rs, ps, conn);
        }
    }

    // 辅助方法：关闭数据库资源
    private void closeResources(ResultSet rs, PreparedStatement stmt, Connection conn) {
        try {
            if (rs != null) rs.close();
        } catch (SQLException e) {
            System.err.println("关闭ResultSet失败: " + e.getMessage());
        }

        try {
            if (stmt != null) stmt.close();
        } catch (SQLException e) {
            System.err.println("关闭PreparedStatement失败: " + e.getMessage());
        }

        try {
            if (conn != null) conn.close();
        } catch (SQLException e) {
            System.err.println("关闭Connection失败: " + e.getMessage());
        }
    }
}